package com.example.keepserver.repository;

import com.example.keepserver.model.Activity;
import com.example.keepserver.model.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Repository
public interface ActivityRepository extends JpaRepository<Activity, String> {
    
    List<Activity> findByUser(User user);
    
    List<Activity> findByUserAndStartTimeBetween(User user, LocalDateTime startDate, LocalDateTime endDate);
    
    List<Activity> findByStartTimeBetween(LocalDateTime startDate, LocalDateTime endDate);
    
    List<Activity> findByUserAndType(User user, String type);
    
    List<Activity> findByUserAndTypeAndStartTimeBetween(User user, String type, LocalDateTime startDate, LocalDateTime endDate);
    
    @Query("SELECT a.type as type, COUNT(a) as count FROM Activity a WHERE a.startTime BETWEEN :startDate AND :endDate GROUP BY a.type")
    List<Map<String, Object>> countActivitiesByType(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    @Query("SELECT FUNCTION('DATE_FORMAT', a.startTime, '%Y-%m-%d') as date, COUNT(DISTINCT a.user) as activeUsers FROM Activity a WHERE a.startTime BETWEEN :startDate AND :endDate GROUP BY FUNCTION('DATE_FORMAT', a.startTime, '%Y-%m-%d')")
    List<Map<String, Object>> countActiveUsersByDate(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    @Query("SELECT COUNT(DISTINCT a.user) FROM Activity a WHERE a.startTime BETWEEN :startDate AND :endDate")
    Integer countUniqueUsers(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    @Query("SELECT a.user.userId as userId, a.type as type, COUNT(a) as count FROM Activity a WHERE a.startTime BETWEEN :startDate AND :endDate GROUP BY a.user.userId, a.type")
    List<Map<String, Object>> countActivitiesByUserAndType(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    @Query("SELECT FUNCTION('DATE_FORMAT', a.startTime, '%Y-%m-%d') as date, COUNT(a) as count, SUM(a.distance) as totalDistance, SUM(a.duration) as totalDuration, SUM(a.calorie) as totalCalorie FROM Activity a WHERE a.startTime BETWEEN :startDate AND :endDate GROUP BY FUNCTION('DATE_FORMAT', a.startTime, '%Y-%m-%d')")
    List<Map<String, Object>> getStatsByDate(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
} 